﻿using DevExpress.XtraEditors;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mSA.Common
{
    public class ExcelHelpers
    {
        public static bool DataTableToExcel(DataTable dt, string FilePath,bool flag_Sum)
        {
            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            int iRow = 0;
            int iCol = 0;
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    // workbook = new XSSFWorkbook();//office 2007

                    // 2007版本  
                    if (FilePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook();
                    // 2003版本  
                    else if (FilePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook();

                    sheet = workbook.CreateSheet("Sheet1");//创建一个名称为Sheet0的表 
                    int rowCount = dt.Rows.Count;//行数
                    int columnCount = dt.Columns.Count;//列数
                                                       //设置列头  
                    row = sheet.CreateRow(0);//excel第一行设为列头  
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                    }
                    //行颜色--黄色
                    ICellStyle s = workbook.CreateCellStyle();
                    s.FillForegroundColor = HSSFColor.Yellow.Index;
                    s.FillPattern = FillPattern.SolidForeground;

                    //行颜色--蓝色
                    //ICellStyle blue = workbook.CreateCellStyle();
                    //blue.FillForegroundColor = HSSFColor.LightBlue.Index;
                    //blue.FillPattern = FillPattern.SolidForeground;
                    ICellStyle blue = workbook.CreateCellStyle();
                    blue.FillForegroundColor = 0;
                    blue.FillPattern = FillPattern.SolidForeground;
                    ((XSSFColor)blue.FillForegroundColorColor).SetRgb(new byte[] { 219, 229, 241 });


                    string firstCellValue = "";//第一列的值
                    int balck = 1;
                    int addback = 0;
                    //设置每行每列的单元格,  
                    for (int i = 0; i <= rowCount; i++)
                    {
                        iRow = i;//定义错误行赋值
                        row = sheet.CreateRow(i + addback + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            if (flag_Sum)
                            {
                                //处理最后一行
                                if (i == rowCount)
                                {
                                    //整行赋予颜色
                                    for (int m = 0; m < columnCount; m++)
                                    {
                                        cell = row.CreateCell(m);
                                        cell.CellStyle = s;
                                        //求和函数
                                        if (dt.Columns[m].ColumnName == "出货数量")
                                            cell.SetCellFormula(string.Format("SUM(K{0}:K{1})", balck, i + addback + 1));
                                        else if (dt.Columns[m].ColumnName == "重量")
                                            cell.SetCellFormula(string.Format("SUM(M{0}:M{1})", balck, i + addback + 1));
                                        else if (dt.Columns[m].ColumnName == "体积")
                                            cell.SetCellFormula(string.Format("SUM(L{0}:L{1})", balck, i + addback + 1));

                                    }
                                    // balck = i + 3;
                                    firstCellValue = "";
                                    break;
                                }
                                else if (dt.Rows[i][0].ToString() != firstCellValue && firstCellValue != "")
                                {
                                    //整行赋予颜色
                                    for (int m = 0; m < columnCount; m++)
                                    {
                                        cell = row.CreateCell(m);
                                        cell.CellStyle = s;
                                        //求和函数
                                        if (dt.Columns[m].ColumnName == "出货数量")
                                            cell.SetCellFormula(string.Format("SUM(K{0}:K{1})", balck, i + 1 + addback));
                                        else if (dt.Columns[m].ColumnName == "重量")
                                            cell.SetCellFormula(string.Format("SUM(M{0}:M{1})", balck, i + 1 + addback));
                                        else if (dt.Columns[m].ColumnName == "体积")
                                            cell.SetCellFormula(string.Format("SUM(L{0}:L{1})", balck, i + 1 + addback));

                                    }
                                    balck = i + 3 + addback;
                                    firstCellValue = "";
                                    i--;//增加了空白行,需要减回去
                                    addback++;
                                    break;
                                }

                            }

                            if (i == rowCount)
                                break;

                            //转换为数字格式
                            iCol = j;
                            if (dt.Columns[j].ColumnName == "体积" || dt.Columns[j].ColumnName == "重量" ||
                                dt.Columns[j].ColumnName == "出货数量" || dt.Columns[j].ColumnName == "库存数量" ||
                                dt.Columns[j].ColumnName == "项目" || dt.Columns[j].ColumnName == "上层项目")
                            {
                                double values = dt.Rows[i][j].ToString().Length > 0 ?
                                    Convert.ToDouble(dt.Rows[i][j].ToString()) : 0;
                                cell = row.CreateCell(j, CellType.Numeric);
                                cell.SetCellValue(values);
                            }
                            else
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                            firstCellValue = dt.Rows[i][0].ToString();

                        }

                        //体积、重量为空时添加蓝色
                        if (flag_Sum)
                        {
                            if (i < rowCount)
                            {
                                if (dt.Columns.Contains("体积") && dt.Columns.Contains("重量"))
                                {
                                    double valuesCBM = dt.Rows[i]["体积"].ToString().Length > 0 ?
                                       Convert.ToDouble(dt.Rows[i]["体积"].ToString()) : 0;
                                    double valuesWeight = dt.Rows[i]["重量"].ToString().Length > 0 ?
                                      Convert.ToDouble(dt.Rows[i]["重量"].ToString()) : 0;
                                    if (valuesCBM == 0 || valuesWeight == 0)
                                    {
                                        for (int m = 0; m < columnCount; m++)
                                        {
                                            row.GetCell(m).CellStyle = blue;
                                        }
                                    }
                                }
                            }
                        }
                    }
                    using (fs = File.OpenWrite(FilePath))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据  
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {

                if (fs != null)
                {
                    fs.Close();
                }
                XtraMessageBox.Show("第" + iRow.ToString() + "行" + "第" + iCol.ToString() + "列" + ex.Message);
                return false;

            }
        }

    }
}
